---
description: 'How to work with Nitric SQL Databases and Prisma'
tags:
  - Databases & CMS
  - ORMs
languages:
  - typescript
  - javascript
published_at: 2024-06-14
---

# Nitric SQL Databases with Prisma

This guide demonstrates how to use [Prisma ORM](https://www.prisma.io) with Nitric's [SQL database](/sql) feature. For this guide, we will be creating a to-do app. The finished source can be found [here](https://github.com/nitrictech/examples/tree/main/v1/nitric-prisma).

<Note>
  SQL databases are currently in preview and only support PostgreSQL deployed to
  AWS.
</Note>

## Create a new Nitric project

The first step is to create a new Nitric TypeScript project using the [Nitric CLI](/reference/cli).

```bash
nitric new todo-app ts-starter
```

## Enabling SQL databases

SQL databases are currently in Preview. To enable this feature in your project, add the following to your `nitric.yaml` file

```yaml
preview:
  - sql-databases
```

Ensure your CLI is at least `v1.42` and your AWS provider is at least `v1.6.2`.

## Install Prisma

```bash
npm install prisma @prisma/client
npx prisma init
```

We install `prisma` for database schema and migration management, and `@prisma/client` for database interactions.

## Create a Nitric SQL Database and Connect Prisma to it

```ts {{title: 'db.ts'}}
import { sql } from '@nitric/sdk'
import { PrismaClient } from '@prisma/client'

const db = sql('todos', {
  // points to our custom prisma migration dockerfile
  migrations: 'dockerfile://migrate.dockerfile',
})

let prisma: PrismaClient

const getClient = async () => {
  // ensure we only create the client once
  if (!prisma) {
    const connectionString = await db.connectionString()

    prisma = new PrismaClient({
      datasources: {
        db: {
          url: connectionString,
        },
      },
    })
  }
  return prisma
}

// export our getClient function, which will be used to get the prisma client during runtime
export default getClient
```

In this snippet, we declare a new database called `todos` with the `sql` resource from `@nitric/sdk`, then we import and configure `PrismaClient` to connect to the database using Nitric's connection string.

## Add the migrations dockerfile for cloud deployments

```docker {{title: 'migrate.dockerfile'}}
# Use an official Node.js runtime as the base image
FROM node

ENV DB_URL=""
ENV NITRIC_DB_NAME=""

# Copy package.json and package-lock.json into the Docker image
COPY package*.json ./

# Install the application's dependencies inside the Docker image
RUN npm ci

# Copy the rest of the application into the Docker image
COPY . .

# Generate Prisma client
RUN npx prisma generate

# Run the migrations and start the application when the Docker container starts
ENTRYPOINT ["sh", "-c", "npx prisma migrate deploy"]
```

This will be used during deployment to apply migrations using Prisma before any code changes are applied, ensuring no breakages in code due to a schema change.

## Setup Prisma schema

Next, set up the Prisma schema file. This file contains the database schema and configurations. The `DB_URL` environment variable is used by Nitric during cloud migrations; however, during local development, we can add it via a `.env` file (we will do that later).

```prisma {{title: 'prisma/schema.prisma'}}
datasource db {
  provider = "postgresql"
  url      = env("DB_URL")
}

generator client {
  provider = "prisma-client-js"
}

model Todo {
  id    Int     @id @default(autoincrement())
  text  String
  done  Boolean @default(false)
}
```

This schema defines a `Todo` model with fields `id`, `text`, and `done`.

## Developing the app

### Building the API

Delete the `services/hello.ts` file and create a new one called `todos.ts`, this will house our APIs.

Let's create some endpoints for creating, retrieving, editing, and deleting todo tasks.

```ts {{title: 'services/todos.ts'}}
import { api } from '@nitric/sdk'
import getClient from '../db'

const mainApi = api('main')

// fetch all todos, sorted by their identifier
mainApi.get('/todos', async (ctx) => {
  const db = await getClient()
  const data = await db.todo.findMany({
    orderBy: { id: 'asc' },
  })

  return ctx.res.json(data)
})

// inserts a new record into the todo table
mainApi.post('/todos/:id', async (ctx) => {
  const { id } = ctx.req.params
  const { text } = ctx.req.json()
  const db = await getClient()

  await db.todo.create({
    data: {
      id: parseInt(id),
      text,
    },
  })
})

// updates the text of a todo by its identifier
mainApi.patch('/todos/:id', async (ctx) => {
  const { id } = ctx.req.params
  const { text } = ctx.req.json()
  const db = await getClient()

  await db.todo.update({
    where: { id: parseInt(id) },
    data: { text },
  })
})

// toggles the status of a todo to its opposite state
mainApi.patch('/todos/:id/toggle', async (ctx) => {
  const { id } = ctx.req.params
  const db = await getClient()
  const todo = await db.todo.findUnique({
    where: { id: parseInt(id) },
  })

  if (todo) {
    await db.todo.update({
      where: { id: parseInt(id) },
      data: { done: !todo.done },
    })
  }
})

// removes a todo by its identifier
mainApi.delete('/todos/:id', async (ctx) => {
  const { id } = ctx.req.params
  const db = await getClient()

  await db.todo.delete({
    where: { id: parseInt(id) },
  })
})
```

### Start testing with nitric start

Create a `.env` file with your local database URL so you can develop against it during `nitric start`. By default, this will be:

```bash {{title: '.env'}}
DB_URL="postgresql://postgres:localsecret@localhost:5432/todos"
```

Run nitric start to start your local database and APIs.

```bash
nitric start
```

With your local database now running, create and run your first migration, and generate the Prisma client using the `prisma migrate` command:

```bash
npx prisma migrate dev --name init
```

### Add some todos using the Nitric dashboard

Open the local dashboard at <a target="_blank" href="http://localhost:49152">localhost:49152</a>, then navigate to the `POST /todos/{id}` endpoint and fill in an `id` as the path param.

![Add id param for creating a todo](/docs/images/guides/nitric-and-prisma/step-1.png)

Then add some content to the body of the request, stating the `text` key and value of the todo task and click send.

![Add text content for the todo](/docs/images/guides/nitric-and-prisma/step-2.png)

Let's check our todo got created by calling the `GET /todos` endpoint, which will list all todos.

![Check that our todo get created](/docs/images/guides/nitric-and-prisma/step-3.png)

Let's toggle our todo as done, navigate to the `PATCH /todos/{id}/toggle` and enter the correct `id` to toggle.

![Toggle our todo as done](/docs/images/guides/nitric-and-prisma/step-4.png)

Finally, let's check our todo got toggled to done by calling the `GET /todos` endpoint.

![View the todo change](/docs/images/guides/nitric-and-prisma/step-5.png)

Feel free to test the other endpoints to update or delete the todo items.

## Deploying to AWS

### Create your stack

Create an AWS stack called `aws-staging` for your staging environment.

```bash
nitric stack new aws-staging aws
```

Inside the stack file, ensure you set your `region`.

```yaml title:nitric.dev.yaml
# The nitric provider to use
provider: nitric/aws@latest
# The target AWS region to deploy to
# See available regions:
# https://docs.aws.amazon.com/general/latest/gr/lambda-service.html
region: us-east-2
```

### Deploy

Go ahead and deploy to AWS using the `nitric up` command. Ensure you have set up your [AWS credentials](/providers/pulumi/aws#usage) correctly.

```bash
nitric up
```

### Tear down

To avoid unwanted costs of running your test app, you can tear down the stack using the `nitric down` command.

```bash
nitric down
```

### Prefer to use a different ORM? We also have these guides:

- [Nitric SQL with Drizzle](./nitric-and-drizzle).
